#
# Notes:
#	API Exports to add:			Needs
#	- /char/CharacterSheet.xml.aspx - 	    Limited
#	    - to find corporation of main character
#       - skill tracker for other members of corp?
#
#   - /corp/CorporationSheet.xml.aspx -     Limited
#       - to find CEO of corp
#       - lists hangars
#       - lists wallets
#       - maybe useful for tracking friends/enemies outgame?
#
#   - /corp/AccountBalance.xml.aspx -       Full
#       - show corp balances
#
#   - /corp/AssetList.xml.aspx -            Full
#       - show corp assets
#
#   - /account/Characters.xml.aspx -        Limited
#       - Grab alts easier
#
#   - /corp/IndustryJobs.xml.aspx -         Full
#       - Show industry jobs
#
#   - /corp/WalletJournal.xml.aspx -        Full
#       - Show wallet journal
#
#   - /char/Killlog.xml.aspx -              Full
#       - show kill mail log
#
#   - /corp/MarketOrders.xml.aspx -         Full
#       - show market orders
#
#   - /corp/StarbaseList.xml.aspx -         Full
#       - grab POS list, shows states and position
#
#   - /corp/StarbaseDetail.xml.aspx -       Full
#       - shows pos details and fuel levels, not fuel usage though
#

package MemTrac::Base;
use strict;
use DBI;
use CGI qw/header/;
use lib '..';
use MemTrac::Utils;
use MemTrac::Templates;
use MemTrac::Strings;
use MemTrac::Tables;
use XML::Simple;
use Carp;

require Exporter;
our @ISA = qw(Exporter);
our @EXPORT = qw( make_error init_memtrac run_memtrac table_exists init_member_table );
our @EXPORT_OK = qw();

my $query = new CGI;
my (%c,%s,$dbh);

sub init_memtrac {
    if(!-e "config.xml") {
        my $msg;
        if ( $query->param("task") eq 'write_initial_conf' ) {
            $c{lang} = $query->param("lang");
            %s = %{get_strings($c{lang})};
            $c{db}{host} = $query->param("host");
            $msg .= "Database Host".$s{missing} unless $query->param("host");
            $c{db}{user} = $query->param("user");
            $msg .= "Database User".$s{missing} unless $query->param("user");
            $c{db}{password} = $query->param("password");
            $msg .= "Database Password".$s{missing} unless $query->param("password");
            $c{db}{db} = $query->param("db");
            $msg .= "Database Name".$s{missing} unless $query->param("db");
            $c{auth}{userid} = $query->param("userid");
            $msg .= "User ID".$s{missing} unless $query->param("userid");
            $c{auth}{apikey} = $query->param("apikey");
            $msg .= "API Key".$s{missing} unless $query->param("apikey");
            $c{auth}{characterID} = $query->param("characterID");
            $msg .= "Character ID".$s{missing} unless $query->param("characterID");
            $c{db}{prefix} = $query->param("prefix");

            if(!$msg) {
                $dbh=DBI->connect(
                "DBI:mysql:database=$c{db}{db};host=$c{db}{host}",
                $c{db}{user},
                $c{db}{password},
                {AutoCommit=>1}
                ) or $msg = "$s{db}{sql_err}<br>".$DBI::errstr;
            }
        
            if(!$msg) {        
                my @tables = get_tables();
                for my $table (@tables) {
                    $table = $c{db}{prefix}.$table;
                    $msg = $table.$s{db}{exist_err} if(table_exists($table));        
                }
            }
        
            $msg = check_table_status() if !$msg;
        
            $msg = refresh_database() if !$msg;
        
            config_save() if !$msg;

            make_http_forward(".") if !$msg;
        }
    
        $c{db}{host} = "localhost" unless defined $c{db}{host};
        $c{db}{prefix} = "mt_" unless defined $c{db}{prefix};
        my @langs = @{get_langs()};    
        print   header(),
                BASIC_HEAD_TEMPLATE->(),
                CONFIG_FORM->(langs=>\@langs,lang=>$c{lang},characterID=>$c{auth}{characterID},userid=>$c{auth}{userid},
                              apikey=>$c{auth}{apikey},db=>$c{db}{db},host=>$c{db}{host},user=>$c{db}{user},password=>$c{db}{password},
                              prefix=>$c{db}{prefix},msg=>$msg),
                BASIC_FOOT_TEMPLATE->();
        exit;
    }
        
    %c = %{XMLin("config.xml")};
    %s = %{get_strings($c{lang})};
    
    $dbh=DBI->connect(
                            "DBI:mysql:database=$c{db}{db};host=$c{db}{host}",
                            $c{db}{user},
                            $c{db}{password},
                            {AutoCommit=>1}
                        ) or make_error($s{db}{conn_err});
    
    my $msg = check_table_status();
    make_error($msg) if $msg;
}

################################################################################

sub run_memtrac {
    
    my $task=($query->param("task") or $query->param("action"));
    my $output;
    
    if(!$task) {
        my $norefresh = $query->param("norefresh");
        $output = get_list($norefresh);
    }
    elsif($task eq "toggle_dir") {
        my $id = $query->param("id");
        $output = toggle_director($id);
    }
    elsif($task eq "show_alt_choice") {
        my $id = $query->param("id");
        $output = show_alt_choice($id);
    }
    elsif($task eq "toggle_alt") {
        my $id = $query->param("id");
        $output = toggle_alt($id);
    }
    elsif($task eq "make_alt") {
        my $alt_id = $query->param("alt_id");
        my $main = $query->param("main");
        $output = make_alt($alt_id, $main);
    }
  
    $dbh->disconnect();
    return  header(),
            BASIC_HEAD_TEMPLATE->(),
            $output,
            BASIC_FOOT_TEMPLATE->();
}

sub get_list {
    my ($norefresh) = @_;
    my $output;
    my @member_list;
    my %alts;
    my %types;
    my $member_list_expiry;

    my $table = $c{db}{prefix}."expiries_generic";
    my $sth = $dbh->prepare("SELECT expiry FROM ".$table." WHERE type='members';") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute() or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    while(my $row=$sth->fetchrow_hashref()) {
        $member_list_expiry = $row->{expiry};
    }
    
    my $msg = refresh_database() unless ( $norefresh or ($member_list_expiry > time()) );
    make_error($msg) if $msg;
    
    $table = $c{db}{prefix}."alts";
    $sth = $dbh->prepare("SELECT * FROM ".$table.";") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute() or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    while(my $row=$sth->fetchrow_hashref()) {
        $alts{$row->{altID}} = $row->{alt_of};
    }
    
    $table = $c{db}{prefix}."types";
    $sth = $dbh->prepare("SELECT * FROM ".$table.";") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute() or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    while(my $row=$sth->fetchrow_hashref()) {
        $types{$row->{typeID}} = $row->{type};
    }
    
    $table = $c{db}{prefix}."members";
    $sth = $dbh->prepare("SELECT * FROM ".$table." ORDER BY logoffDateTime desc;") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute() or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    while(my $row=$sth->fetchrow_hashref()) {
        $row->{type} = $types{$row->{characterID}} ? $types{$row->{characterID}} :0;
        $row->{alt_of} = $alts{$row->{characterID}};
        push @member_list,$row;
    }
    
    my @final_list;
    for my $row (@member_list) {
        push @final_list, $row if ( ($row->{type} == 1) && !$row->{alt_of});
    }
    
    for my $row (@member_list) {
        push @final_list, $row if ( ($row->{type} != 1) && !$row->{alt_of});
    }
    
    for my $row (@member_list) {
        if($row->{alt_of}) {
            for my $main_row (@final_list) {
                push @{$main_row->{alts}}, $row if ($main_row->{characterID} == $row->{alt_of});
            }
        }
    }
    
    my $next_refresh = make_duration(time(),$member_list_expiry);
        
    $output = MEMBERLIST_TABLE->(member_list=>\@final_list,next_refresh=>$next_refresh);
    
    return $output;
}

sub toggle_director {
    my ($id) = @_;
    my $del_id;
    
    my $table = $c{db}{prefix}."types";
    
    my $sth = $dbh->prepare("SELECT * FROM ".$table." WHERE typeID = ?;") or make_error("$s{db}{sql_err}<br>$dbh->errstr<br>$id<br>");
    $sth->execute($id) or make_error("$s{db}{sql_err}<br>$dbh->errstr<br>$id<br>");
    
    if($sth->fetchrow_hashref()) {
        $sth = $dbh->prepare("DELETE FROM ".$table." WHERE typeID = ?;") or make_error("$s{db}{sql_err}<br>$dbh->errstr<br>$id<br>");
    }
    else {
        $sth = $dbh->prepare("INSERT into ".$table." (typeID, type) values (?,1);") or make_error("$s{db}{sql_err}<br>$dbh->errstr<br>$id<br>");
    }

    $sth->execute($id) or make_error("$s{db}{sql_err}<br>$dbh->errstr<br>$id<br>");
    make_http_forward(".");
}

sub make_alt {
    my ($alt_id, $main) = @_;
    my $table = $c{db}{prefix}."alts";
    
    if($main == -1) {
        my $sth = $dbh->prepare("DELETE FROM ".$table." WHERE altID = ?;") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
        $sth->execute($alt_id) or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
        make_http_forward(".");
    }

    my $sth = $dbh->prepare("INSERT into ".$table." (altID, alt_of) values (?,?);") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute($alt_id,$main) or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    
    make_http_forward(".");
}

sub show_alt_choice {
    my ($alt_id) = @_;
    
    my (@chars,$alt_name);
    
    my $table = $c{db}{prefix}."members";
    my $alt_table = $c{db}{prefix}."alts";
    
    my $sth = $dbh->prepare("
        SELECT *
        FROM ".$table." mt
        LEFT JOIN ".$alt_table." at
        ON characterID = altID
        WHERE alt_of IS NULL
        ORDER BY name asc;") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute() or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    while(my $row=$sth->fetchrow_hashref()) { 
        push @chars,$row;
    }
    
    $sth = $dbh->prepare("SELECT name FROM ".$table." WHERE characterID = ?;") or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute($alt_id) or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    while(my $row=$sth->fetchrow_hashref()) { 
        $alt_name = $row->{name};
    }    
    
    my $output = ALT_FORM->(chars=>\@chars,alt_id=>$alt_id,alt_name=>$alt_name);
    
    return $output;
}

sub refresh_database {
    my $table = $c{db}{prefix}."members";
    my $result = get_raw_api_data();
    return $s{no_api_err} unless $result;
    return $result if $result =~ m/^Error Code /;
    my %m = %{$result};
    
    my $time_diff = time() - datetime2epoch($m{currentTime});
    print $time_diff;
    
    my $sth = $dbh->prepare("TRUNCATE TABLE ".$table.";");
    $sth->execute();
    
    my @fields = keys %{get_structure("members")};
    my $sql = "INSERT into $table (".join(', ',@fields).") values (".join(', ',('?')x@fields).");";
    $sth = $dbh->prepare($sql) or make_error($s{db}{sql_err} .': refresh_db_prepare<br>'. $dbh->errstr . "<br>$sql<br>");
    
    for my $key (keys %{$m{result}{rowset}{row}}) {
        my %formdata;
        my $char = $m{result}{rowset}{row}{$key};
        for my $attrib (keys %{$char}) {
            $formdata{$attrib} = $attrib =~ m/DateTime/ ? (datetime2epoch($char->{$attrib})+$time_diff) : $char->{$attrib};
        }
        $formdata{name} = $key;
        $sth->execute(@formdata{@fields}) or make_error($s{db}{sql_err} .': refresh_db_execute<br>'. $dbh->errstr . "<br>$sql<br>");
    }

    my $member_list_expiry = datetime2epoch($m{cachedUntil}) - datetime2epoch($m{currentTime}) + time();

    $table = $c{db}{prefix}."expiries_generic";
    $sth = $dbh->prepare("INSERT INTO ".$table." (type,expiry) VALUES (?,?) ON DUPLICATE KEY UPDATE expiry=?;")
        or make_error("$s{db}{sql_err}<br>".$dbh->errstr);
    $sth->execute("members",$member_list_expiry,$member_list_expiry) or make_error("$s{db}{sql_err}<br>".$dbh->errstr);

    return 0;
}


sub get_raw_api_data {
    use LWP::Simple;
    use XML::Simple;
    
    my $data = get(
        "http://api.eve-online.com/corp/MemberTracking.xml.aspx?".
        "characterID=$c{auth}{characterID}&userid=$c{auth}{userid}&apikey=$c{auth}{apikey}"
    );
    
    my %m = %{XMLin($data)};
    return ("Error Code $m{error}{code}: $m{error}{content}") if defined $m{error}{content};
    
    return \%m;
}

sub check_table_status {
    my @tables = get_tables();
    my $msg;
    
    for my $table (@tables)  {
        $msg = init_table($table) if(!table_exists($table));
        return $msg if $msg;
    }
}

sub init_table {
    my ($table) = @_;
    my ($sth,@fields);
    
    my %struc = %{get_structure($table)};

    $table = $c{db}{prefix}.$table;
    
    for my $field (keys %struc) {
        push @fields, "$field $struc{$field}";
    }
    
    my $sql = "CREATE TABLE $table (".join(', ',@fields).");";

    $sth=$dbh->do("DROP TABLE ".$table.";") if(table_exists($table));    
    $sth=$dbh->prepare($sql) or return $s{db}{sql_err}.$dbh->errstr;
    $sth->execute() or return $s{db}{sql_err}.$dbh->errstr;
    
    return 0;
}

sub table_exists {
    my ($table)=@_;
    my ($sth);

    $table = $c{db}{prefix}.$table;
    
    return 0 unless($sth=$dbh->prepare("SELECT * FROM ".$table." LIMIT 1;"));
    return 0 unless($sth->execute());
    return 1;
}

sub make_error {
    my ($error)=@_;
  
    if($dbh) {
        $dbh->{Warn}=0;
        $dbh->disconnect();
    }
  
    print     header(),
              BASIC_HEAD_TEMPLATE->(),
              ERROR_TEMPLATE->(error=>$error),
              BASIC_FOOT_TEMPLATE->();
  
    exit;
}

sub make_http_forward {
    my ($location)=@_;
  
    if($dbh) {
        $dbh->{Warn}=0;
        $dbh->disconnect();
    }

    print "Status: 303 Go West\n";
    print "Location: $location\n";
    print "Content-Type: text/html\n";
    print "\n";
    print '<html><body><a href="'.$location.'">'.$location.'</a></body></html>';
  
    exit;
}

sub config_save {    
    my $config = XMLout(\%c);

    if ( !-e 'config.xml' ) {
        if ( -w '.' ) {
            open my $WRITE, ">config.xml";
            print $WRITE $config;
            close $WRITE;
        }
        else {
            make_error("Could not write settings file, please paste the following
                       into a file named 'config.xml' and upload it into the
                       working directory of your MemTrac installation:</h1>
                       <p class='error'>" . clean_string($config) . "</p><h1>");
        }
    }
    else {
        if ( !-w 'config.xml' ) {
            chmod 0666, 'config.xml';
        }
        if ( -w 'config.xml' ) {
            open my $WRITE, ">config.xml";
            print $WRITE $config;
            close $WRITE;
        }
        else {
            make_error("Could not write settings file, please chmod it to 0666
                       or update it with the following contents:</h1>
                       <p class='error'>" . clean_string($config) . "</p><h1>");
        }
    }
}


1;
